package org.dbi.junit;

import static org.junit.Assert.*;

import java.util.ArrayList;

import org.dbi.beans.Attribute;
import org.dbi.beans.AttributeGroup;
import org.dbi.mysql.Rdbms;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class TestRdbms {
	private Rdbms myRdbms = null;

	@Before
	public void setUp() throws Exception {
		myRdbms = Rdbms.getInstance();
		myRdbms.init();
	}

	@After
	public void tearDown() throws Exception {
		myRdbms = null;

	}

	@Test
	public void testGetInstance() {
		Rdbms newRdbms = Rdbms.getInstance();
		assertNotNull(newRdbms);
	}

	@Test
	public void testInit() {
		assertTrue(myRdbms.init());
	}

	@Test
	public void testGetRdbmsDatabases() {
		assertNotNull(myRdbms.getRdbmsDatabasesAsString());
	}

	@Test
	public void testCreateTableInExistDb() {
		myRdbms.createDatabase("Db1");
		myRdbms.createTable("Grp1", "Tbl1", "Db1");
		// WallyTestDB wordt bij de init eral aan toegevoegd
		assertEquals("[Db1, WallyTestDB]", myRdbms.getRdbmsDatabasesAsString());
	}

	@Test
	public void testCreateTableInNotExistDb() {
		assertFalse(myRdbms.createTable("Grp1", "Tbl1", "NotExistDb"));
	}

	@Test
	public void testCreateTableInExistDbWithDuplicateGroup() {
		// Grp1 bestaat al uit de methode van hierboven
		assertFalse(myRdbms.createTable("Grp1", "Tbl2", "Db1"));
	}

	@Test
	public void testCreateAttributeInExistTabel() {
		assertTrue(myRdbms.createAttribute("attrib1", "Db1", "Tbl1", "varchar", true));

	}

	@Test
	public void testCreateAttributeInNotExistTabel() {
		assertFalse(myRdbms.createAttribute("attrib1", "Db1", "Tbl2", "varchar", true));
	}

	@Test
	public void testCreateDuplicateAttributeInNotExistTabel() {
		assertTrue(myRdbms.createAttribute("attrib2", "Db1", "Tbl1", "varchar", true));
		assertFalse(myRdbms.createAttribute("attrib2", "Db1", "Tbl1", "varchar", true));

	}

	@Test
	public void testCreateAttributeInNotExistDatabase() {
		assertFalse(myRdbms.createAttribute("attrib2", "Db2", "Tbl1", "varchar", true));
	}

	public void testCreatePrimaryKeyInTable() {
		assertTrue(myRdbms.createTable("cons1234", "myConsTable", "WallyTestDB"));
		assertTrue(myRdbms.createAttribute("Artiest", "WallyTestDB", "myConsTable", "varchar", true));
		assertTrue(myRdbms.createAttribute("Album", "WallyTestDB", "myConsTable", "varchar", true));

		ArrayList<Object[]> attributes = new ArrayList<Object[]>();
		Object[] Artiest = { "Artiest", "varchar", true };
		Object[] Album = { "Album", "varchar", true };
		attributes.add(Artiest);
		attributes.add(Album);

		assertTrue(myRdbms.createConstraint("cons1234", "PK1", "ag", attributes));

	}

	@Test
	public void testCreateInvalidKeyInTable() {
		ArrayList<Object[]> attributes = new ArrayList<Object[]>();
		Object[] Artiest = { "Artiest", "varchar", true };
		Object[] Album = { "Album", "varchar", true };
		attributes.add(Artiest);
		attributes.add(Album);

		assertFalse(myRdbms.createConstraint("cons1234", "aloha", "ag", attributes));
	}

	@Test
	public void testUseDatabaseTrue() {
		assertTrue(myRdbms.useDatabase("WallyTestDB"));
	}

	//
	@Test
	public void testUseDatabaseFalse() {
		assertFalse(myRdbms.useDatabase("Rammstein"));
	}

	@Test
	public void testCreateDatabaseTrue() {
		assertTrue(myRdbms.createDatabase("DBI"));
	}

	@Test
	public void testCreateDatabaseFalse() {
		assertFalse(myRdbms.createDatabase("DBI"));
	}

	@Test
	public void testGetCurrentDatabase() {
		myRdbms.useDatabase("DBI");
		assertEquals("DBI", myRdbms.getCurrentDatabase());
	}

	//
	@Test
	public void testDropDatabase() {
		myRdbms.createDatabase("DBI");
		myRdbms.useDatabase("WallyTestDB");
		assertTrue(myRdbms.dropDatabase("DBI"));
	}

	@Test
	public void testDropDatabaseInUse() {
		myRdbms.createDatabase("DBI");
		myRdbms.useDatabase("DBI");
		assertFalse(myRdbms.dropDatabase("DBI"));
	}

	@Test
	public void testDropDatabaseNotExist() {
		assertFalse(myRdbms.dropDatabase("DBI"));
	}

	@Test
	public void tblUpdateDatabaseNaam() {
		System.out.println("==update==");
		myRdbms.useDatabase("WallyTestDB");
		// gaat niet goed. Type is niet gelijk namelijk : tinyint - varchar
		assertFalse(myRdbms.createReference("REF789", "G5", "G400"));
	}

	@Test
	public void testInsertWithRefCheck() {
		String[] DbcolumnName = { "Naam" };
		Object[] DBvalue = { new String("PleunDb") };
		assertTrue(myRdbms.insert("tblDatabase", DbcolumnName, DBvalue));

		myRdbms.useDatabase("PleunDb");

		String[] DbcolumnName1 = { "Naam" };
		Object[] DBvalue1 = { new String("TestDb") };
		assertTrue(myRdbms.insert("tblDatabase", DbcolumnName1, DBvalue1));

		String[] TblcolumnName = { "Naam", "tblDatabase", "tblConstraint" };
		Object[] Tblvalue = { new String("tblVis"), new String("PleunDb"), new String("kont1") };
		assertTrue(myRdbms.insert("tblTabel", TblcolumnName, Tblvalue));

		String[] TblcolumnNameFail = { "Naam", "tblDatabase", "tblConstraint" };
		Object[] TblvalueFail = { new String("tblVis"), new String("Test"), new String("kont1") };
		assertFalse(myRdbms.insert("tblTabel", TblcolumnNameFail, TblvalueFail));
	}

	@Test
	public void testInsertWithRefCheckCompleteDbStructure() {

		String[] DbcolumnName = { "Naam" };
		Object[] DBvalue = { new String("RefDB") };
		assertTrue(myRdbms.insert("tblDatabase", DbcolumnName, DBvalue));

		myRdbms.useDatabase("WallyTestDB");

		// Duplicate Database - False
		String[] DbcolumnNameDup = { "Naam" };
		Object[] DBvalueDup = { new String("RefDB") };
		assertFalse(myRdbms.insert("tblDatabase", DbcolumnNameDup, DBvalueDup));

		// Database voor Tabel bestaat niet - False
		String[] TblcolumnNameFail = { "Naam", "tblConstraint", "tblDatabase" };
		Object[] TblvalueFail = { new String("tblVis"), new String("Test"), new String("kont1") };
		assertFalse(myRdbms.insert("tblTabel", TblcolumnNameFail, TblvalueFail));

		String[] TblcolumnName = { "Naam", "tblDatabase", "tblConstraint" };
		Object[] Tblvalue = { new String("tblUberChill"), new String("RefDB"), new String("klont1") };
		assertTrue(myRdbms.insert("tblTabel", TblcolumnName, Tblvalue));

		String[] TblcolumnName2 = { "Naam", "tblDatabase", "tblConstraint" };
		Object[] Tblvalue2 = { new String("tblUberZon"), new String("RefDB"), new String("klont2") };
		assertTrue(myRdbms.insert("tblTabel", TblcolumnName2, Tblvalue2));

		String[] AttribcolumnName = { "Naam", "tblTabel", "tblDatabase", "ColumnType", "Mandatory" };
		Object[] Attribvalue = { new String("att12222"), new String("tblUberChill"), new String("RefDB"), new String("tinyint"), new Boolean(true) };
		assertTrue(myRdbms.insert("tblAttribute", AttribcolumnName, Attribvalue));

		// Tabel voor deze attribute bestaat niet.
		String[] FailAttribcolumnName = { "Naam", "tblTabel", "tblDatabase", "ColumnType", "Mandatory" };
		Object[] FailAttribvalue = { new String("att1"), new String("tblUberBalen"), new String("RefFB"), new String("varchar"), new String("1") };
		assertFalse(myRdbms.insert("tblAttribute", FailAttribcolumnName, FailAttribvalue));

		String[] ConscolumnName = { "Naam", "PK_FK_ALTKEY_REF", "tblGroup" };
		Object[] Consvalue = { new String("klont1"), new String("PK100"), new String("G555") };
		assertTrue(myRdbms.insert("tblConstraint", ConscolumnName, Consvalue));

		String[] ConscolumnName2 = { "Naam", "PK_FK_ALTKEY_REF", "tblGroup" };
		Object[] Consvalue2 = { new String("klont2"), new String("PK101"), new String("G557") };
		assertTrue(myRdbms.insert("tblConstraint", ConscolumnName2, Consvalue2));

		String[] ConscolumnName3 = { "Naam", "PK_FK_ALTKEY_REF", "tblGroup" };
		Object[] Consvalue3 = { new String("klont2"), new String("ref89"), new String("") };
		assertTrue(myRdbms.insert("tblConstraint", ConscolumnName3, Consvalue3));

		// Constraint bestaat niet
		String[] ConscolumnNameFail = { "Naam", "PK_FK_ALTKEY_REF", "tblGroup" };
		Object[] ConsvalueFail = { new String("klont4"), new String("PK106"), new String("G556") };
		assertFalse(myRdbms.insert("tblConstraint", ConscolumnNameFail, ConsvalueFail));

		// Ref bestaat niet
		String[] RefcolumnNameFail = { "Naam", "FromGroup", "ToGroup" };
		Object[] RefvalueFail = { new String("ref99"), new String("G555"), new String("G557") };
		assertFalse(myRdbms.insert("tblReference", RefcolumnNameFail, RefvalueFail));

		String[] RefcolumnName = { "Naam", "FromGroup", "ToGroup" };
		Object[] Refvalue = { new String("ref89"), new String("G555"), new String("G557") };
		assertTrue(myRdbms.insert("tblReference", RefcolumnName, Refvalue));

		String[] AttribGroupcolumnName = { "Naam", "AttribNaam", "tblTabel", "tblDatabase" };
		Object[] AttribGroupvalue = { new String("G557"), new String("att12222"), new String("tblUberChill"), new String("RefDB") };
		assertTrue(myRdbms.insert("tblAttribGroup", AttribGroupcolumnName, AttribGroupvalue));

	}

	@Test
	public void testInsert() {
		String[] columnName = { "Naam" };
		Object[] value = { new String("InsertTest") };
		assertTrue(myRdbms.insert("tblDatabase", columnName, value));
	}

	@Test
	public void testDeleteTabel() {
		assertFalse(myRdbms.dropTable("tblDatabase"));
		assertFalse(myRdbms.dropTable("tblTabel"));
		assertFalse(myRdbms.dropTable("tblAttribute"));
		assertTrue(myRdbms.dropTable("tblAttribGroup"));
		assertFalse(myRdbms.dropTable("tblConstraint"));
		assertTrue(myRdbms.dropTable("tblReference"));
	}

	@Test
	public  void deleteRecord(){
		String[] DbcolumnName = { "Naam" };
		Object[] DBvalue = { new String("DeleteRecordDB") };
		assertTrue(myRdbms.insert("tblDatabase", DbcolumnName, DBvalue));


		String[] TblcolumnName = { "Naam", "tblDatabase", "tblConstraint" };
		Object[] Tblvalue = { new String("tblDelete"), new String("DeleteRecordDB"), new String("conssss") };
		assertTrue(myRdbms.insert("tblTabel", TblcolumnName, Tblvalue));

		assertFalse(myRdbms.deleteRecord("tblDatabase", DbcolumnName, DBvalue));
		assertTrue(myRdbms.deleteRecord("tblTabel", TblcolumnName, Tblvalue));
		assertTrue(myRdbms.deleteRecord("tblDatabase", DbcolumnName, DBvalue));
	}
	@Test
	public void testInsertAlreadyExists() {
		String[] columnName = { "Naam" };
		Object[] value = { new String("InsertTest") };
		assertFalse(myRdbms.insert("tblDatabase", columnName, value));
	}

	@Test
	public void testInsertWrongColumnName() {
		String[] columnName = { "IkBeStaNiet" };
		Object[] value = { new String("InsertTest") };
		assertFalse(myRdbms.insert("tblDatabase", columnName, value));
	}

	@Test
	public void testInsertTblTable() {

		String[] columnName1 = { "Naam" };
		Object[] value1 = { new String("InsertDB") };
		assertTrue(myRdbms.insert("tblDatabase", columnName1, value1));

		String[] columnName = { "Naam", "tblDatabase", "tblConstraint" };
		Object[] value = { new String("InsertTest123"), new String("InsertDB"), new String("cons1") };
		assertTrue(myRdbms.insert("tblTabel", columnName, value));
	}

	@Test
	public void testSelect() {
		System.out.println("==");
		assertEquals(6, myRdbms.select("tblDatabase").size());
	}

	@Test
	public void testSelectColumnName() {
		String[] columnNames = { "Naam" };
		System.out.println("==");
		assertEquals(6, myRdbms.select("tblDatabase", columnNames).size());

	}

	@Test
	public void testSelectWhere() {
		String[] columnName1 = { "Naam" };
		Object[] value1 = { new String("InsertDB123") };
		assertTrue(myRdbms.insert("tblDatabase", columnName1, value1));

		String[] columnNames = { "Naam" };
		Object[] whereValues = { "InsertDB123" };
		System.out.println("==");
		assertEquals(1, myRdbms.select("tblDatabase", columnNames, columnNames, whereValues).size());
	}

	@Test
	public void testCreateReferenceWithDiffertColumnSize() {
		myRdbms.useDatabase("WallyTestDB");
		// gaat niet goed. Size is niet gelijk namelijk ; 3 - 1
		assertFalse(myRdbms.createReference("REF8999", "G8", "G5"));
	}

	@Test
	public void testCreateReferenceWithDuplicateRefName() {
		myRdbms.useDatabase("WallyTestDB");
		// gaat niet goed. Naam bestaat al
		assertFalse(myRdbms.createReference("REF8999", "G8", "G5"));
	}

	@Test
	public void testCreateReferenceWithDiffertColumnType() {
		myRdbms.useDatabase("WallyTestDB");
		// gaat niet goed. Type is niet gelijk namelijk : tinyint - varchar
		assertFalse(myRdbms.createReference("REF9898", "G8", "G1000"));

	}

	@Test
	public void tblUpdateDatabaseWallyTestDBMagNietVanwegeRefCheck() {
		String[] columnName1 = { "Naam" };
		Object[] value1 = { new String("TestDB") };
		assertTrue(myRdbms.insert("tblDatabase", columnName1, value1));

		String[] columnName = { "Naam", "tblDatabase", "tblConstraint" };
		Object[] value = { new String("tblUpdate"), new String("TestDB"), new String("cons1") };
		assertTrue(myRdbms.insert("tblTabel", columnName, value));

		String[] columnsToEdit = { "Naam" };
		Object[] newValues = { "Wallah" };
		String[] whereColumnNamesForUpdate = { "Naam" };
		Object[] whereValuesForUpdate = { "TestDB" };


		// assertEquals(2, myRdbms.select("tblDatabase").size());

		assertEquals(8, myRdbms.select("tblDatabase").size());

		assertFalse(myRdbms.update("tblDatabase", columnsToEdit, newValues, whereColumnNamesForUpdate, whereValuesForUpdate));

		// assertEquals(2, myRdbms.select("tblDatabase").size());

		assertEquals(8, myRdbms.select("tblDatabase").size());


		String[] tblcolumnsToEdit = { "tblDatabase" };
		Object[] tblnewValues = { "Wallah" };
		String[] tblwhereColumnNamesForUpdate = { "tblDatabase" };
		Object[] tblwhereValuesForUpdate = { "TestDB" };
		assertFalse(myRdbms.update("tblTabel", tblcolumnsToEdit, tblnewValues, tblwhereColumnNamesForUpdate, tblwhereValuesForUpdate));

		// assertEquals(2, myRdbms.select("tblTabel").size());

		assertEquals(6, myRdbms.select("tblTabel").size());

	}
	
	@Test
	public void tblUpdateWrongColumn() {
	    assertEquals(6, myRdbms.select("tblTabel").size());
	    String[] insertColumnNames = { "Artiest", "Album" };
		Object[] insertValues = { "Rammstein", "Herzeleid" };

		assertTrue(myRdbms.createTable("groupie", "PenW", "WallyTestDB"));
		assertTrue(myRdbms.createAttribute("Artiest", "WallyTestDB", "PenW", "varchar", true));
		assertTrue(myRdbms.createAttribute("Album", "WallyTestDB", "PenW", "varchar", true));

		ArrayList<Object[]> attributes = new ArrayList<Object[]>();
		Object[] Artiest = { "Artiest", "varchar", true };
		Object[] Album = { "Album", "varchar", true };
		attributes.add(Artiest);
		attributes.add(Album);

		assertTrue(myRdbms.createConstraint("groupie", "PK1", "ag", attributes));

		assertTrue(myRdbms.insert("PenW", insertColumnNames, insertValues));

		assertEquals(2, myRdbms.select("PenW").size());

		System.out.println("==update==");

		String[] columnsToEdit = { "DBI" };
		Object[] newValues = { "Sehnsucht" };
		String[] whereColumnNamesForUpdate = { "Album" };
		Object[] whereValuesForUpdate = { "Herzeleid" };
		System.out.println("jojojojo");
		assertFalse(myRdbms.update("PenW", columnsToEdit, newValues, whereColumnNamesForUpdate, whereValuesForUpdate));
		assertEquals(2, myRdbms.select("PenW").size());
	}

	   @Test
       public void tblUpdateValueCanBeUpdated() {
               String[] insertColumnNames = { "Artiest", "Album", "Tracks" };
               Object[] insertValues = { "Rammstein", "Herzeleid", 11 };

               assertTrue(myRdbms.createTable("groupie2", "Muziek", "WallyTestDB"));
               assertTrue(myRdbms.createAttribute("Artiest", "WallyTestDB", "Muziek", "varchar", true));
               assertTrue(myRdbms.createAttribute("Album", "WallyTestDB", "Muziek", "varchar", true));
               assertTrue(myRdbms.createAttribute("Tracks", "WallyTestDB", "Muziek", "integer", true));

               ArrayList<Object[]> pkAttribs = new ArrayList<Object[]>();
               Object[] Artiest = { "Artiest", "varchar", true };
               Object[] Album = { "Album", "varchar", true };
               pkAttribs.add(Artiest);
               pkAttribs.add(Album);

               assertTrue(myRdbms.createConstraint("groupie2", "PK1", "ag", pkAttribs));

               assertTrue(myRdbms.insert("Muziek", insertColumnNames, insertValues));

               
               assertEquals(2, myRdbms.select("Muziek").size());
               
               String[] columnsToEdit = { "Tracks" };
               Object[] newValues = { 12 };
               String[] whereColumnNamesForUpdate = { "Album" };
               Object[] whereValuesForUpdate = { "Herzeleid" };
               
               assertTrue(myRdbms.update("Muziek", columnsToEdit, newValues, whereColumnNamesForUpdate, whereValuesForUpdate));
               assertEquals(2, myRdbms.select("Muziek").size());

       }
       @Test
       public void tblUpdateValueCanBeUpdatedTwo() {
               String[] insertColumnNames = { "Artiest", "Album", "Tracks", "Uitgever" };
               Object[] insertValues = { "Rammstein", "Herzeleid", 11, "Universal"};

               assertTrue(myRdbms.createTable("groupie3", "Rammstein", "WallyTestDB"));
               assertTrue(myRdbms.createAttribute("Artiest", "WallyTestDB", "Rammstein", "varchar", true));
               assertTrue(myRdbms.createAttribute("Album", "WallyTestDB", "Rammstein", "varchar", true));
               assertTrue(myRdbms.createAttribute("Tracks", "WallyTestDB", "Rammstein", "integer", true));
               assertTrue(myRdbms.createAttribute("Uitgever", "WallyTestDB", "Rammstein", "varchar", true));

               ArrayList<Object[]> pkAttribs = new ArrayList<Object[]>();
               Object[] Artiest = { "Artiest", "varchar", true };
               Object[] Album = { "Album", "varchar", true };
               pkAttribs.add(Artiest);
               pkAttribs.add(Album);

               assertTrue(myRdbms.createConstraint("groupie3", "PK1", "ag", pkAttribs));

               assertTrue(myRdbms.insert("Rammstein", insertColumnNames, insertValues));

               
               assertEquals(2, myRdbms.select("Rammstein").size());
               
               String[] columnsToEdit = { "Tracks", "Uitgever" };
               Object[] newValues = { 12 , "Saxion" };
               String[] whereColumnNamesForUpdate = { "Album" };
               Object[] whereValuesForUpdate = { "Herzeleid" };
               
               assertTrue(myRdbms.update("Rammstein", columnsToEdit, newValues, whereColumnNamesForUpdate, whereValuesForUpdate));
               assertEquals(2, myRdbms.select("Rammstein").size());

	}
	@Test
	public void tblUpdateValueCannotBeUpdated() {
	    assertEquals(2, myRdbms.select("tblAttribute").size());
	    String[] columnsToEdit = { "Naam", "Mandatory" };
		Object[] newValues = { "waffelstein" , false };
		String[] whereColumnNamesForUpdate = { "Naam" };
		Object[] whereValuesForUpdate = { "att12222" };
		
		assertFalse(myRdbms.update("tblAttribute", columnsToEdit, newValues, whereColumnNamesForUpdate, whereValuesForUpdate));
		assertEquals(2, myRdbms.select("tblAttribute").size());

       }



}